<?php

class PortfolioTable extends Doctrine_Table
{
    
    public static function getInstance()
    {
        return Doctrine_Core::getTable('Portfolio');
    }
    
    public function getAllCategoriesWithItems()
    {
        // SELECT * FROM portfolio p 
        // INNER JOIN portfolio_tag pt ON p.portfolio_tag_id = pt.id 
        // GROUP BY pt.title 
        // ORDER BY pt.title ASC;
        $q = Doctrine_Query::create()
                ->select()
                ->from('portfolio p')
                ->innerJoin('p.PortfolioTag pt ON p.portfolio_tag_id = pt.id')
                ->groupBy('pt.title')
                ->orderBy('pt.title ASC');
        
        return $q->execute();
        //echo $q->getSqlQuery();        
    }
    
    public function getAllItems($tagname)
    {
        // SELECT * FROM portfolio p 
        // INNER JOIN portfolio_tag pt ON p.portfolio_tag_id = pt.id 
        // INNER JOIN portfolio_image pi ON p.id = pi.portfolio_id
        // WHERE pt.title = 'Website' 
        // ORDER BY pt.title ASC;
        $q = Doctrine_Query::create()
                ->select()
                ->from('portfolio p')
                ->innerJoin('p.PortfolioTag pt ON p.portfolio_tag_id = pt.id')
                //->innerJoin('p.PortfolioImage pi ON p.id = pi.portfolio_id')
                ->where('pt.title = ?', $tagname)
                ->orderBy('pt.title ASC');
        
        return $q->execute();
        //echo $q->getSqlQuery();
    }
    
    public function getPortfolioItem($portfolioId)
    {
        // SELECT * FROM portfolio p
        // LEFT JOIN portfolio_tag pt ON p.portfolio_tag_id = pt.id
        // LEFT JOIN portfolio_image pi ON p.id = pi.portfolio_id
        // WHERE p.id = $portfolioId
        $q = Doctrine_Query::create()
                ->select()
                ->from('portfolio p')
                ->leftJoin('p.PortfolioTag pt ON p.portfolio_tag_id = pt.id')
                ->leftJoin('p.PortfolioImage pi ON p.id = pi.portfolio_id')
                //->innerJoin('p.PortfolioTag pt ON p.portfolio_tag_id = pt.id')
                //->innerJoin('p.PortfolioImage pi ON p.id = pi.portfolio_id')
                ->where('p.id = ?', $portfolioId);
        
        return $q->execute();
        //echo $q->getSqlQuery();
    }
    
    public function getImage($portfolioId)
    {
        // SELECT * FROM portfolio_image pi
        // WHERE pi.portfolio_id = $portfolioId
        // ORDER BY pi.id ASC
        // LIMIT 1
        $q = Doctrine_Query::create()
                ->select()
                ->from('PortfolioImage pi')
                ->where('pi.portfolio_id = ?', $portfolioId)
                ->orderBy('pi.id ASC')
                ->limit('1');
        
        return $q->execute();
        //echo $q->getSqlQuery();
    }
    
}